#!/bin/bash
# File Name: copy_binlog.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-04-10 15:47:55
# Function: 
#########################################################################
binlog_path="/mfw_rundata/backup/db/mysql/binlog"
size_log_dir="/home/moshan/script/size"
sql_file="/home/moshan/script/insert.sql"
log_file="/home/moshan/script/log/copy_binlog.log"
mysql_admin_user="moshan"
mysql_admin_passwd="xxxxxx"
mysql_user="dbms_monitor_r"
mysql_passwd="xxxxxxx"
mysql_host="192.168.2.xxx"
mysql_port="3306"
mysql_db="db_cmdb"
mysql_admin_comm="mysql -u${mysql_admin_user} -p${mysql_admin_passwd} -h${mysql_host} -P${mysql_port} ${mysql_db}"
mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${mysql_host} -P${mysql_port} ${mysql_db}"
> ${sql_file}
[ ! -d "${size_log_dir}" ] && mkdir -p ${size_log_dir}
#从cmdb收集高可用组写节点的ip及端口
${mysql_comm} -NBe "select concat(tsi.ip_app ,'_',tdi.port) from t_server_info tsi join t_db_instance tdi on tsi.server_id=tdi.server_id join t_db_ha_group_member tdhgm on tdhgm.member_id=tdi.instance_id join t_db_ha_group tdhg on tdhg.ha_group_id=tdhgm.ha_group_id where tdhgm.role_name='写节点' and tsi.ip_app like '192.%';"|sort -nr|uniq > supply.log 2>/dev/null

for dir in $(cat supply.log)
do
    #以ip_port的格式命名目录，并以此作为循环条件
    host="$(awk -F_ '{print $1}' <<< "${dir}")"
    port="$(awk -F_ '{print $2}' <<< "${dir}")"
    size_log_file="${size_log_dir}/${host}_${port}.size"
    if [ ! -f "${size_log_dir}/${host}_${port}.size" ]
    then
        echo "ip地址             端口号    binlog总大小MB    每小时增长量MB    变更时间" > ${size_log_file}
    fi
    binlog_dir="${binlog_path}/${dir}/"
    #判断该节点是否运行了binlog传输进程
    new_node=$(ps -ef|grep -vE "[0-9]{2}:[0-9]{2}:[0-9]{2} grep "|grep -c -- "/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=${host} --port=${port}")
    if [ "${new_node}x" == "0x" ]
    then
        #如果没运行，则先获取到该节点当前binlog的文件名
        mysqlbin_last="$(mysql -u${mysql_user} -p${mysql_passwd} -h${host} -P${port} -NBe "show binary logs;" 2>/dev/null|tail -1|awk '{print $1}')"
        mkdir -p ${binlog_dir}
        for group_member in $(${mysql_comm} -NBe "select distinct t_server_info.ip_app from t_db_ha_group_member join t_db_instance on t_db_instance.instance_id=t_db_ha_group_member.member_id join t_server_info on t_server_info.server_id=t_db_instance.server_id where t_db_ha_group_member.ha_group_id =(select min(distinct t_db_ha_group_member.ha_group_id) from t_db_ha_group_member join t_db_instance on t_db_instance.instance_id=t_db_ha_group_member.member_id join t_server_info on t_server_info.server_id=t_db_instance.server_id where t_server_info.ip_app='${host}' and t_db_instance.port=${port}) and t_db_instance.port=${port};")
        do
            #根据高可用组写节点的ip获取到整个组的所有ip，目的是为了避免同组内多个节点同时拷贝
            #思路：将除了写节点外的所有节点的binlog传输进程都kill掉（如果进程存在的话）
            if [ "${group_member}x" == "${host}x" ]
            then
                #如果写节点传输binlog的进程不在，则启动该进程
                nohup /usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=${host} --port=${port} --user=dbms_monitor_r --password='4bb51ad0-8964-11e8-b8fe-cf6796b9df33' ${mysqlbin_last} --result-file=${binlog_dir} >/dev/null 2>/dev/null &
                sleep 1
                run_ok=$(ps -ef|grep -vE "[0-9]{2}:[0-9]{2}:[0-9]{2} grep "|grep -c -- "/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=${host} --port=${port}")
                if [ "${run_ok}x" == "1x" ]
                then
                    echo -e "\033[32m[$(date "+%F %H:%M:%S")] [OK] [Successful startup for ${host}:${port}]" >> ${log_file}
                else
                    echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] [Startup failed for ${host}:${port}]" >> ${log_file}
                fi
            else
                group_member_node=$(ps -ef|grep -vE "[0-9]{2}:[0-9]{2}:[0-9]{2} grep "|grep -c -- "/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=${group_member} --port=${port}")
                if [ "${group_member_node}x" == "1x" ]
                then
                    #该操作是kill掉该节点的binlog传输进程，因为该节点非写节点，或者因为某些原因发生了切换
                    ps -ef|grep -- "/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=${group_member} --port=${port}"|awk '{print $2}'|xargs -i kill -9 {} 2>/dev/null
                fi
            fi
        done
    else
        #记录每个实例，每小时的总大小，增长量，及统计时间
        all_binlog_size="$(du -sm ${binlog_dir}|awk '{print $1}')"
        within_for_60min_size="$(find ${binlog_dir} -mmin -60 -print|grep -v "${binlog_dir}$"|xargs -i du -sm {}|awk 'BEGIN{sum=0}{sum+=$1} END{print sum}')"
        #生成sql语句
        echo "insert into binlog_server.t_binlog_grow_info(mysql_host,mysql_port,binlog_all_size,within_for_60min_size,c_time) values('${host}','${port}',${all_binlog_size},${within_for_60min_size},'$(date "+%F %H:%M:%S")');" >> ${sql_file}
        host_len=$((19-${#host}))
        port_len=$((10-${#port}))
        all_size_len=$((18-${#all_binlog_size}))
        little_size_len=$((18-${#within_for_60min_size}))
        if [ "${host_len}" != "0x" ]
        then
            for ((var=0;var<${host_len};var++))
            do
                host="${host} "
            done
        fi
        if [ "${port_len}" != "0x" ]
        then
            for ((var=0;var<${port_len};var++))
            do
                port="${port} "
            done
        fi
        if [ "${all_size_len}" != "0x" ]
        then
            for ((var=0;var<${all_size_len};var++))
            do
                all_binlog_size="${all_binlog_size} "
            done
        fi
        if [ "${little_size_len}" != "0x" ]
        then
            for ((var=0;var<${little_size_len};var++))
            do
                within_for_60min_size="${within_for_60min_size} "
            done
        fi
        echo "${host}${port}${all_binlog_size}${within_for_60min_size}$(date "+%F %H:%M:%S")" >> ${size_log_file}
    fi
done
#写入数据库
cat ${sql_file}|${mysql_admin_comm}
#将多余的进程杀掉
ps -ef|grep -- "/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host="|awk -F/ 'NF-=1{print $NF}'|grep "192.168"|sort -nr > nohup.out
for dir in $(cat nohup.out)
do
    [ "$(grep -c ${dir} supply.log)x" == "1x" ] && continue
    host="$(awk -F_ '{print $1}' <<< "${dir}")"
    port="$(awk -F_ '{print $2}' <<< "${dir}")"
    ps -ef|grep -- "/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=${host} --port=${port}"|awk '{print $2}'|xargs -i kill -9 {} 2>/dev/null
done
[ -f "nohup.out" ] && rm -f nohup.out
[ -f "supply.log" ] && rm -f supply.log
